Replication accounts should not be granted DBA privileges.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15619	DG0100-SQLServer9	SV-24258r1_rule	DCFA-1	Medium

Description
Replication accounts may be used to access databases defined for the replication architecture. An exploit of a replication on one database could lead to the compromise of any database participating in the replication that uses the same account name and credentials. If the replication account is compromised and it has DBA privileges, the database is at additional risk to unauthorized or malicious action.

Description

Replication accounts may be used to access databases defined for the replication architecture. An exploit of a replication on one database could lead to the compromise of any database participating in the replication that uses the same account name and credentials. If the replication account is compromised and it has DBA privileges, the database is at additional risk to unauthorized or malicious action.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-23568r1_chk )
From the query prompt: USE master EXEC SP_GET_DISTRIBUTOR If the value of installed is 0, and a review of the System Security Plan confirms the use of replication is not required and not allowed, this check is Not a Finding. If the value of installed is 1, and a review of the System Security Plan confirms the use of replication is required and allowed, this is Not a Finding. If it is not required or not allowed, this is a Finding. The following steps determine if the security of the configured Replication follows best practices: From the query prompt: EXEC SP_HELPREPLICATIONDBOPTION 1. Ensure replication data is encrypted in transit Review documentation and evidence of configuration for encrypted connections between remote databases participating in replication where transmissions cross untrusted (support connections that do not have a need-to-know access requirement to the data being replicated) networks. 2. Confirm replication agents use dedicated accounts This is covered individually under check DM6065 and is not included in Finding status here. To view replication agent accounts: USE msdb SELECT p.name 'Proxy Name', c.credential_identity FROM sys.credentials c, sysproxies p, sysproxysubsystem s WHERE c.credential_id = p.proxy_id AND s.proxy_id = p.proxy_id AND s.subsysstem_id > 3 AND s.subsystem_id < 9 3. Confirm Replication Agent accounts are assigned minimum privileges For each database, review assigned roles/permissions for each agent account: USE [database name] For each agent account listed under #2 above: EXEC SP_HELPUSER '[user name]' If any GroupName other than db_owner is listed in any database, this is a Finding. If any GroupName is listed in any database other than replication databases, this is a Finding EXEC SP_HELPROTECT '[user name]' If any permission is listed, this is a Finding. Perform once: EXEC SP_HELPSRVROLEMEMBER If any replication agent accounts are listed, this is a Finding. 4. Confirm only authorized Merge and Distribution Agent accounts are listed in the Publication Access List (PAL) For each replication database: EXEC SP_HELPPUBLICATION For each publication listed: EXEC SP_HELP_PUBLICATION_ACCESS '[publication name]' If any accounts are listed under publications that are not SYSADMINs, replication merge (category REPL-Merge) or replication distributor (category REPL-Distribution) agent accounts, this is a Finding. 5. Confirm minimum permissions are assigned to any local snapshot folders Results for this security check are recorded individually under DM6075. 6. (cont from 5) Confirm snapshot Agent accounts are granted only write permissions to the snapshot folder If the snapshot agent account has more than write access to the snapshot folder, this is a Finding. 7. Verify network shares are used for snapshot folders accessed by pull subscriptions If the server does not have a Publisher database, this check is Not a Finding. For each publisher database: USE [database name] EXEC SP_HELPSUBSCRIPTION If any subscribers listed indicate a remote database (a database on a different server), then confirm the snapshot folder is defined as a network share. If it is not, this is a Finding. Note: See folder information for the publication listed for the subscriber under the SP_HELPPUBLICATION results. Windows shares are indicated with a share icon and are indicated as shared in the directory properties \ share tab. 8. Verify Agent accounts use Windows authentication See Agent accounts returned from #2 above If any accounts listed are not Windows accounts (display [domain or computername]\[account name]), this is a Finding.

Check Text ( C-23568r1_chk )

From the query prompt:

USE master
EXEC SP_GET_DISTRIBUTOR

If the value of installed is 0, and a review of the System Security Plan confirms the use of replication is not required and not allowed, this check is Not a Finding.

If the value of installed is 1, and a review of the System Security Plan confirms the use of replication is required and allowed, this is Not a Finding. If it is not required or not allowed, this is a Finding.

The following steps determine if the security of the configured Replication follows best practices:

From the query prompt:

EXEC SP_HELPREPLICATIONDBOPTION

1. Ensure replication data is encrypted in transit

Review documentation and evidence of configuration for encrypted connections between remote databases participating in replication where transmissions cross untrusted (support connections that do not have a need-to-know access requirement to the data being replicated) networks.

2. Confirm replication agents use dedicated accounts

This is covered individually under check DM6065 and is not included in Finding status here. To view replication agent accounts:

USE msdb
SELECT p.name 'Proxy Name', c.credential_identity
FROM sys.credentials c, sysproxies p, sysproxysubsystem s
WHERE c.credential_id = p.proxy_id
AND s.proxy_id = p.proxy_id
AND s.subsysstem_id > 3
AND s.subsystem_id < 9

3. Confirm Replication Agent accounts are assigned minimum privileges

For each database, review assigned roles/permissions for each agent account:

USE [database name]

For each agent account listed under #2 above:

EXEC SP_HELPUSER '[user name]'

If any GroupName other than db_owner is listed in any database, this is a Finding.

If any GroupName is listed in any database other than replication databases, this is a Finding

EXEC SP_HELPROTECT '[user name]'

If any permission is listed, this is a Finding.

Perform once:

EXEC SP_HELPSRVROLEMEMBER

If any replication agent accounts are listed, this is a Finding.

4. Confirm only authorized Merge and Distribution Agent accounts are listed in the Publication Access List (PAL)

For each replication database:

EXEC SP_HELPPUBLICATION

For each publication listed:

EXEC SP_HELP_PUBLICATION_ACCESS '[publication name]'

If any accounts are listed under publications that are not SYSADMINs, replication merge (category REPL-Merge) or replication distributor (category REPL-Distribution) agent accounts, this is a Finding.

5. Confirm minimum permissions are assigned to any local snapshot folders
Results for this security check are recorded individually under DM6075.

6. (cont from 5) Confirm snapshot Agent accounts are granted only write permissions to the snapshot folder

If the snapshot agent account has more than write access to the snapshot folder, this is a Finding.

7. Verify network shares are used for snapshot folders accessed by pull subscriptions

If the server does not have a Publisher database, this check is Not a Finding.

For each publisher database:

USE [database name]
EXEC SP_HELPSUBSCRIPTION

If any subscribers listed indicate a remote database (a database on a different server), then confirm the snapshot folder is defined as a network share. If it is not, this is a Finding.

Note: See folder information for the publication listed for the subscriber under the SP_HELPPUBLICATION results. Windows shares are indicated with a share icon and are indicated as shared in the directory properties \ share tab.

8. Verify Agent accounts use Windows authentication

See Agent accounts returned from #2 above

If any accounts listed are not Windows accounts (display [domain or computername]\[account name]), this is a Finding.

Fix Text (F-20088r1_fix)
Disable replication if replication is not required. From the SQL Server Management Studio GUI: 1. Expand SQL Server 2. Right-click on Replication 3. Click Disable Publishing and Distribution 4. Complete the steps presented Secure replication if required, authorized and documented. 1. Create and use dedicated Windows-authenticated database accounts for Replication Agent use 2. Assign minimum database and file permissions to the Replication Agent accounts 3. Add only authorized Replication Merge and Distribution Agent accounts (and SYSADMIN accounts) to the PAL 4. Use network shared for snapshot folders access by pull subscriptions Document replication in the System Security Plan, AIS Functional Architecture documentation and authorize with the IAO regardless of requirement.

Fix Text (F-20088r1_fix)

Disable replication if replication is not required.

From the SQL Server Management Studio GUI:

1. Expand SQL Server
2. Right-click on Replication
3. Click Disable Publishing and Distribution
4. Complete the steps presented

Secure replication if required, authorized and documented.

1. Create and use dedicated Windows-authenticated database accounts for Replication Agent use
2. Assign minimum database and file permissions to the Replication Agent accounts
3. Add only authorized Replication Merge and Distribution Agent accounts (and SYSADMIN accounts) to the PAL
4. Use network shared for snapshot folders access by pull subscriptions

Document replication in the System Security Plan, AIS Functional Architecture documentation and authorize with the IAO regardless of requirement.